package student.aistar.dao.impl;

import student.aistar.dao.ITVideoDao;
import student.aistar.dao.IUserDao;
import student.aistar.entity.TVideo;
import student.aistar.entity.User;
import student.aistar.util.DateUtil;
import student.aistar.util.JdbcTemplate;
import student.aistar.util.JdbcUtil;
import student.aistar.util.PreparedStatementCallBack;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * 持久层的实现类 - jdbc代码
 */
public class UserDaoImpl implements IUserDao {

    @Override
    public List<User> findAll() {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<User> list = new ArrayList<>();
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/m503", "root", "Abc1234!");
            //获取preparedStatement语句
            statement = conn.createStatement();
            //定义sql语句
            String sql = "select id, username from user";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getInt(1) + resultSet.getString("username"));
                User user = new User();
                user.setId(resultSet.getInt(1));
                user.setUsername(resultSet.getString("username"));
                list.add(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public Integer delById(Integer id) {
        Connection connection = null;
        Statement statement = null;
        int flag = 0;
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql = "delete from user where id =" + id;
            flag = statement.executeUpdate(sql);
            if(flag > 0){
                System.out.println("删除成功");
            }
            else
                System.out.println("删除失败");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return flag;
    }

    @Override
    public User getByUsername(String username) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        User user = new User();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select id, username from user where username = '" + username +"'";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                user.setId(resultSet.getInt(1));
                user.setUsername(resultSet.getString(2));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return user;
    }

    @Override
    public int save(User user) {
        Connection connection = null;
        Statement statement = null;
        int flag;
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String bt = DateUtil.format(user.getCreateDate(),"yyyy-MM-dd");
            System.out.println(bt);
            String sql = "insert into user values(" + user.getId() + ", '" + user.getUsername() + "', '" + user.getPassword() + "', '"
                    + user.getEmail() + "', '" + bt + "')";

            System.out.println(sql);
            flag = statement.executeUpdate(sql);
            if(flag > 0)
                System.out.println("插入成功");
            else
                System.out.println("插入失败");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtil.close(connection, statement);
        }

        return 0;
    }

    @Override
    public int update(User user) {
        Connection connection = null;
        Statement statement = null;
        int flag = 0;
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql = "update user set username='"+user.getUsername()+"', password='" + user.getPassword() + "', email='" +
                    user.getEmail() + "', create_date='" + user.getCreateDate() + "' where id=" + user.getId();
            System.out.println(sql);
            flag = statement.executeUpdate(sql);
            if(flag > 0)
                System.out.println("更新成功");
            else
                System.out.println("更新失败");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtil.close(connection, statement);
        }
        return flag;
    }

    @Override
    public User getById(int id) {
        Connection connection = null;
        Statement statement = null;
        User user = new User();
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select * from user where id=" + id;
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                user.setId(resultSet.getInt(1));
                user.setUsername(resultSet.getString(2));
                user.setPassword(resultSet.getString(3));
                user.setEmail(resultSet.getString(4));
                user.setCreateDate(resultSet.getDate(5));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtil.close(connection, statement);
        }
        return user;
    }


    //第二题: 根据id删除用户,并且把这个用户下的所有的发布的视频全部删除 - 级联删除.
    @Override
    public void delByIdAndVideo(int id) {
        Connection connection = null;
        Statement statement = null;
        int flag1 = 0;
        int flag2 = 0;
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql = "delete from t_video where user_id=" + id;
            System.out.println(sql);
            flag1 = statement.executeUpdate(sql);
            String sql2 = "delete from user where id=" + id;
            flag2 = statement.executeUpdate(sql2);

            if(flag1 > 0 && flag2 > 0){
                System.out.println("根据用户id删除视频成功");
            }
            else
                System.out.println("根据用户id删除视频失败");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtil.close(connection, statement);
        }
    }


    ////根据id查询用户,如果该用户发布了视频信息,查询这个用户下发布的视频信息 - 级联查询
    @Override
    public List<TVideo> findVideoByUserId() {
        List<TVideo> list = new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select t_video.* from user join t_video on user.id = t_video.user_id";
            System.out.println(sql);
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                TVideo tVideo = new TVideo();
                tVideo.setId(resultSet.getInt(1));
                tVideo.setTitle(resultSet.getString(2));
                tVideo.setCreateDate(resultSet.getDate(3));
                tVideo.setPrice(resultSet.getDouble(4));
                tVideo.setVideoUrl(resultSet.getString(5));
                list.add(tVideo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                JdbcUtil.close(connection, statement, resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return list;
    }


    //保存一个用户的同时也保存了这个用户的三个视频信息 - 级联插入
    public void saveVideoOfUser(User user, List<TVideo> list){
        save(user);
        ITVideoDao itVideoDao = new TVideoDaoImpl();
        for (int i = 0; i < list.size(); i++) {
            itVideoDao.insert(list.get(i));
        }
    }

    @Override
    public User findUerWithVideos(Integer id) {
        Connection connection = null;
        PreparedStatement p1 = null;
        PreparedStatement p2 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        User user = new User();
        List<TVideo> list = new ArrayList<>();
        try {
            connection = JdbcUtil.getConnection();
            String sql1 = "select * from user where id = ?";
            p1 = connection.prepareStatement(sql1);
            p1.setObject(1, id);
            rs1 = p1.executeQuery();
            if(rs1.next()){
                user.setId(rs1.getInt(1));
                user.setUsername(rs1.getString(2));
                user.setPassword(rs1.getString(3));
                user.setEmail(rs1.getString(4));
                user.setCreateDate(rs1.getDate(5));

                String sql2 = "select * from t_video where user_id=?";
                p2 = connection.prepareStatement(sql2);
                p2.setObject(1, id);
                rs2 = p2.executeQuery();
                while(rs2.next()){
                    TVideo v = new TVideo();
                    v.setId(rs2.getInt(1));
                    v.setTitle(rs2.getString(2));
                    //v.setCreateDate(rs2.getDate(3));

                    v.setCreateDate(rs2.getTimestamp(3));

                    v.setPrice(rs2.getDouble(4));
                    v.setVideoUrl(rs2.getString(5));

                    //双向关联
                    v.setUser(user);

                    list.add(v);
                }
                user.setVideoList(list);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return user;
    }

    @Override
    public List<User> findAllUsersWithVideos() {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<User> users = new ArrayList<>();
        try {
            conn = JdbcUtil.getConnection();
            String sql = "select user.*, t_video.* from user join t_video on user.id = t_video.user_id";
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            Set<User> set = new HashSet<>();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setEmail(rs.getString(4));
                user.setCreateDate(rs.getDate(5));
                System.out.println(user);

                TVideo tVideo = new TVideo();
                tVideo.setId(rs.getInt(6));
                tVideo.setTitle(rs.getString(7));
                tVideo.setCreateDate(rs.getDate(8));
                tVideo.setPrice(rs.getDouble(9));
                tVideo.setVideoUrl(rs.getString(10));

                if (set.contains(user)){
                    List<TVideo> list = user.getVideoList();

                    list.add(tVideo);
                }else {
                    List<TVideo> tVideos = new ArrayList<>();
                    tVideos.add(tVideo);
                    user.setVideoList(tVideos);
                    set.add(user);
                    users.add(user);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return users;
    }


    /**
     * 统计每个用户发布的视频的数量
     *
     * 前面的User作为不同用户
     * 后面的Integer统计视频个数
     * @return
     */
    @Override
    public Map<User, Integer> countVideo() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<User, Integer> map = new HashMap<>();
        try {
            connection = JdbcUtil.getConnection();
            String sql = "select user.*, count(t_video.id) from user left join t_video on user.id = user_id GROUP BY user.id";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                User user = new User();
                user.setId(resultSet.getInt(1));
                user.setUsername(resultSet.getString(2));
                user.setPassword(resultSet.getString(3));
                user.setCreateDate(resultSet.getDate(5));

                map.put(user, resultSet.getInt(6));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                JdbcUtil.close(connection,preparedStatement,resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return map;
    }

    /**
     * 如果仅仅给定了一个用户名 - 根据用户名进行一个分页查询
     * @param userName
     * @return
     */
    @Override
    public List<User> pageByUser(String userName) {
        return null;
    }

}
